from flask_sqlalchemy import SQLAlchemy
from datetime import datetime

db = SQLAlchemy()

class Grade(db.Model):
    __tablename__ = '年级'
    id = db.Column('编号', db.Integer, primary_key=True)
    name = db.Column('年级名称', db.String(50), nullable=False, unique=True)
    sort_order = db.Column('排序', db.Integer)
    created_at = db.Column('创建时间', db.DateTime, default=datetime.now)
    updated_at = db.Column('更新时间', db.DateTime, default=datetime.now, onupdate=datetime.now)

class Semester(db.Model):
    __tablename__ = '学期'
    id = db.Column('编号', db.Integer, primary_key=True)
    name = db.Column('学期名称', db.String(50), nullable=False, unique=True)
    sort_order = db.Column('排序', db.Integer)
    created_at = db.Column('创建时间', db.DateTime, default=datetime.now)
    updated_at = db.Column('更新时间', db.DateTime, default=datetime.now, onupdate=datetime.now)

class Project(db.Model):
    __tablename__ = '项目'
    id = db.Column('编号', db.Integer, primary_key=True)
    name = db.Column('收费项目', db.String(100), nullable=False)
    sort_order = db.Column('排序', db.Integer)
    created_at = db.Column('创建时间', db.DateTime, default=datetime.now)
    updated_at = db.Column('更新时间', db.DateTime, default=datetime.now, onupdate=datetime.now)

class Account(db.Model):
    __tablename__ = '账号'
    id = db.Column('编号', db.Integer, primary_key=True)
    account = db.Column('账号', db.String(100), nullable=False, unique=True)

    def __repr__(self):
        return f'<Account {self.account}>'

class Parent(db.Model):
    __tablename__ = '家长'
    id = db.Column('编号', db.Integer, primary_key=True)
    name = db.Column('家长姓名', db.String(50), nullable=False)
    phone = db.Column('手机号', db.String(20))
    students = db.relationship('Student', backref='parent', lazy=True)

class Student(db.Model):
    __tablename__ = '学生'
    id = db.Column('编号', db.Integer, primary_key=True)
    name = db.Column('姓名', db.String(50), nullable=False)
    gender = db.Column('性别', db.String(10))
    birth_date = db.Column('出生日期', db.Date)
    id_card = db.Column('身份证号码', db.String(18))
    address = db.Column('家庭住址', db.String(200))
    parent_id = db.Column('家长编号', db.Integer, db.ForeignKey('家长.编号'))
    parent_name = db.Column('家长姓名', db.String(50))
    phone = db.Column('手机号', db.String(20))
    parent_relation = db.Column('家长', db.String(20))

class Class(db.Model):
    __tablename__ = '开班'
    id = db.Column('编号', db.Integer, primary_key=True)
    grade = db.Column('年级', db.String(50))
    year = db.Column('年度', db.String(20))
    semester = db.Column('学期', db.String(20))

class Registration(db.Model):
    __tablename__ = '注册'
    id = db.Column('编号', db.Integer, primary_key=True)
    student_id = db.Column('学生编号', db.Integer, db.ForeignKey('学生.编号'))
    class_id = db.Column('班级编号', db.Integer, db.ForeignKey('开班.编号'))
    year = db.Column('年度', db.String(20))
    semester = db.Column('学期', db.String(20))
    grade = db.Column('年级', db.String(50))
    name = db.Column('姓名', db.String(50))
    gender = db.Column('性别', db.String(10))
    parent_relation = db.Column('家长', db.String(20))
    student = db.Column('学生', db.String(50))

class Payment(db.Model):
    __tablename__ = '收费'
    id = db.Column('编号', db.Integer, primary_key=True)
    date = db.Column('日期', db.Date, default=datetime.now)
    registration_id = db.Column('注册编号', db.Integer, db.ForeignKey('注册.编号'))
    student = db.Column('学生', db.String(50))
    class_name = db.Column('班级', db.String(50))
    year = db.Column('年度', db.String(20))
    semester = db.Column('学期', db.String(20))
    grade = db.Column('年级', db.String(50))
    summary = db.Column('摘要', db.String(200))
    project = db.Column('收费项目', db.String(100))
    amount = db.Column('金额', db.Float)
    account = db.Column('收款账号', db.String(50))
    column1 = db.Column('列1', db.String(100))
    column2 = db.Column('列2', db.String(100))

class PaymentSummary(db.Model):
    __tablename__ = '横向汇总'
    registration_id = db.Column('注册编号', db.Integer, primary_key=True)
    student = db.Column('学生', db.String(50))
    class_name = db.Column('班级', db.String(50))
    fees = db.Column('收费项目金额', db.JSON, default=dict)
    total = db.Column('合计', db.Float)

    def update_fee(self, project_name, amount, is_add=True):
        if self.fees is None:
            self.fees = {}
        
        if project_name not in self.fees:
            self.fees[project_name] = 0
        
        if is_add:
            self.fees[project_name] += amount
        else:
            self.fees[project_name] -= amount
        
        # 更新总金额
        self.total = sum(self.fees.values())

    def get_fee(self, project_name):
        if self.fees is None or project_name not in self.fees:
            return 0
        return self.fees[project_name] 